﻿
Imports System.Data
Imports Sybase.Data.AseClient   
Imports System.Configuration


Public Class CBaseDeDatosSyBase


Private cnnstr As String      
Private cnx As AseConnection 


	Public Sub New()		
        Me.cnnstr = CEncripcion.Decrypt(ConfigurationManager.ConnectionStrings("AseConexion").ConnectionString, "AJnx8d73") 'Se obtiene la cadena de conexión		
	End Sub

    Public Function CrearConexion(ByRef sMsgError As String) As AseConnection 	
		Try
			cnx = New AseConnection (Me.cnnstr )
		Catch ex As AseException 
		    sMsgError = ex.Message  
			cnx = Nothing
		End Try
		Return cnx

	End Function


    Private Sub CargarParametros(ByRef pm As List(Of AseParameter), ByRef cmd As AseCommand )    
		cmd.Parameters.Clear()
		For Each parametro As AseParameter  In pm
			cmd.Parameters.Add(parametro)
		Next
	End Sub
   

    Private Sub RegresarParametros(ByRef pm As List(Of AseParameter), ByRef cmd As AseCommand , ByVal intParamIgnorados As Integer)
	
		pm.Clear()

		For i As Integer = 0 To (cmd.Parameters.Count - intParamIgnorados - 1)
				pm.Add(cmd.Parameters.Item(i))
		Next

	End Sub


    Private Function GenerarCadenaParamSP(ByVal intNumParam As Integer) As String

		Dim sCadenaRet As String
		Dim i As Integer

		sCadenaRet = "("
		For i = 1 To intNumParam
			sCadenaRet = sCadenaRet & "?,"
		Next

		If sCadenaRet.Length() > 1 Then
		   sCadenaRet = sCadenaRet.Substring(0, sCadenaRet.Length - 1)
		End If

		sCadenaRet = sCadenaRet & ")"

		Return sCadenaRet
	End Function


    Public Function EjecutarConsulta(ByVal sSql As String, ByRef pm As List(Of AseParameter), ByRef sMsgError As String) As DataTable
	

		Dim rd		As AseDataReader 
		Dim dt		As DataTable
		Dim cmd		As AseCommand 
		Dim dbCnn   As AseConnection 

		sMsgError = ""


		'1) Se valida la creación y apertura de la conexión a la base de datos
		dbCnn = CrearConexion(sMsgError)
		If Not dbCnn Is Nothing Then
			Try
				 dbCnn.Open()
			Catch ex As Exception
			   dbCnn.Dispose()
			   sMsgError = ex.Message
			   Return Nothing
			End Try
		Else
		   Return Nothing
		End If


		'Se ejecuta la consulta sql con sus parámetros

		 cmd = New AseCommand 
		 Try
			cmd.CommandType = CommandType.Text
			cmd.Connection = dbCnn
			cmd.CommandTimeout = 0

			cmd.CommandText = sSql
			CargarParametros(pm, cmd)

			rd = cmd.ExecuteReader()

			dt = New DataTable()
			dt.Load(rd)
			rd.Close()

		Catch ex As Exception

			sMsgError = ex.Message
			Return Nothing

		Finally

		   dbCnn.Close()
		   dbCnn.Dispose()
		   cmd.Dispose()
		End Try

		Return dt
	End Function


    Public Function EjecutarComando(ByVal sSql As String, ByRef pm As List(Of AseParameter), ByRef afectados As Integer, ByRef sMsgError As String) As Boolean
	

		Dim dbCnn   As AseConnection 
		Dim dbTrans As AseTransaction 
		Dim cmd     As AseCommand 

		sMsgError = ""
		afectados = 0

        
        


		'1) Se valida la creación y apertura de la conexión a la base de datos
		dbCnn = CrearConexion(sMsgError)
		If Not dbCnn Is Nothing Then
			Try
				 dbCnn.Open()
			Catch ex As Exception
			   dbCnn.Dispose()
			   sMsgError = ex.Message
			   Return False
			End Try
		Else
		   Return false
		End If


		'2) Se establece una transacción 
		dbTrans = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted)

		'3) Se ejecuta el comando  o bloque de comandos 
		cmd = New AseCommand 
		Try
			cmd.CommandType = CommandType.Text
			cmd.Connection = dbCnn
			cmd.CommandTimeout = 0

			cmd.CommandText = sSql
			CargarParametros(pm, cmd)


			cmd.Transaction = dbTrans
			afectados = cmd.ExecuteNonQuery()
			dbTrans.Commit()   'Se completa la transacción			

		Catch ex As Exception
			dbTrans.Rollback()	'En caso de error se reversa las operaciones realizadas		
			sMsgError = ex.Message
			Return False

		Finally
			dbCnn.Close()
			dbCnn.Dispose()
			dbTrans.Dispose()
			cmd.Dispose()

		End Try

		Return True

	End Function
    

    Public Function EjecutarSPConsulta(ByVal sNombreSP As String, ByRef pm As List(Of AseParameter ), ByRef sMsgError As String) As DataTable
	

		Dim rd       As AseDataReader 
		Dim dt       As New DataTable
		Dim dbCnn    As AseConnection 
		Dim dbTrans  As AseTransaction 
		Dim cmd      As AseCommand 

		sMsgError = ""



		'1) Se valida la creación y apertura de la conexión a la base de datos
		dbCnn = CrearConexion(sMsgError)
		If Not dbCnn Is Nothing Then
			Try
				 dbCnn.Open()
			Catch ex As Exception
			   dbCnn.Dispose()
			   sMsgError = ex.Message
			   Return Nothing
			End Try
		Else
		   Return Nothing
		End If


		'2) Se establece una transacción 
		dbTrans = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted)


		'Se ejecuta la consulta sql con sus parámetros
		cmd = New AseCommand ()
		Try
			cmd.CommandType = CommandType.Text
			cmd.Connection = dbCnn
			cmd.CommandTimeout = 0


			Dim parm As AseParameter 
			CargarParametros(pm, cmd)

			'Se agregan dos parámetros para el manejo de excepciones dentro del SP, los cuales son adicionales a los párametros entrada originales. 

			parm = cmd.Parameters.Add(New AseParameter("@SQL_CODRET", AseDbType.Integer))
			parm.Direction = ParameterDirection.Output

			parm = cmd.Parameters.Add(New AseParameter("@SQL_MSGERROR", AseDbType.VarChar, 30))
			parm.Direction = ParameterDirection.Output

			cmd.CommandText = "CALL " & sNombreSP & GenerarCadenaParamSP(cmd.Parameters.Count)


			cmd.Transaction = dbTrans
			rd = cmd.ExecuteReader()
		                 
			
			Dim intSQLCodret As Integer = CType(cmd.Parameters("@SQL_CODRET").Value, Int32)
			Dim strSQLMsgError As String = CType(cmd.Parameters("@SQL_MSGERROR").Value, String)


			RegresarParametros(pm, cmd, 2)


			If intSQLCodret = 0 Then
				dt.Load(rd)
				rd.Close()
				dbTrans.Commit()
			Else
				dbTrans.Rollback()
				sMsgError = "Error: SQLCODE " & intSQLCodret.ToString() & ",  " & strSQLMsgError
				dt = Nothing
			End If


		Catch ex As Exception
			sMsgError = ex.Message
			Return Nothing
		Finally
		   dbCnn.Close()
		   dbCnn.Dispose()
		End Try

		Return dt
	End Function


   Public Function EjecutarSPComandos(ByVal sNombreSP As String, ByRef pm As List(Of AseParameter), ByRef sMsgError As String) As Boolean 
	
			
		Dim dbCnn   As AseConnection 
		Dim dbTrans As AseTransaction 
		Dim cmd     As AseCommand 

		sMsgError = ""


		'1) Se valida la creación y apertura de la conexión a la base de datos
		dbCnn = CrearConexion(sMsgError)
		If Not dbCnn Is Nothing Then
			Try
				 dbCnn.Open()
			Catch ex As Exception
			   dbCnn.Dispose()
			   sMsgError = ex.Message
			   Return False 
			End Try
		Else
		   Return False 
		End If


		'2) Se establece una transacción 
		dbTrans = dbCnn.BeginTransaction(IsolationLevel.ReadCommitted)


		'Se ejecuta la consulta sql con sus parámetros
		cmd = New AseCommand 
		Try
			cmd.CommandType = CommandType.StoredProcedure 
			cmd.Connection = dbCnn
			cmd.CommandTimeout = 0


			Dim parm As AseParameter 
			CargarParametros(pm, cmd)
			parm = cmd.Parameters.Add(New AseParameter("@SQL_CODRET",  AseDbType.Integer))
			parm.Direction = ParameterDirection.Output
			parm = cmd.Parameters.Add(New AseParameter("@SQL_MSGERROR", AseDbType.VarChar, 60))
			parm.Direction = ParameterDirection.Output



			cmd.CommandText =  sNombreSP 


			cmd.Transaction = dbTrans
			cmd.ExecuteNonQuery ()


			Dim intSQLCodret As Integer = CType(cmd.Parameters("@SQL_CODRET").Value, Int32)
			Dim strSQLMsgError As String = CType(cmd.Parameters("@SQL_MSGERROR").Value, String)

			'Se eliminan los dos parámetros adicionales para el manejo de excepciones y sólo se mantienen los parámetros de entrada oriigiales.
			RegresarParametros(pm, cmd, 2)


			If intSQLCodret = 0 Then				
				dbTrans.Commit()
				Return true
			Else
				dbTrans.Rollback()
				sMsgError = "Error: SQLCODE " & intSQLCodret.ToString() & ",  " & strSQLMsgError	
				Return False 			
			End If

		Catch ex As Exception
		    dbTrans.Rollback()
			sMsgError = ex.Message
			Return false
		Finally
		   dbCnn.Close()
		   dbCnn.Dispose()
		End Try

		
	End Function








End Class
